package Persistencia;

import java.sql.*;
import java.util.Vector;


import Modelo.*;

public class AdministradorPersistenciaTarjeta {
	
	private static AdministradorPersistenciaTarjeta pool;
	
	private AdministradorPersistenciaTarjeta()
	{
		
	}
	
	public static AdministradorPersistenciaTarjeta  getInstance(){
		if (pool == null){
			pool = new AdministradorPersistenciaTarjeta();
		}
		
		return pool;
	}
	
	public void insert (Tarjeta tarjeta , String dni){
		
		Connection con = PoolConnection.getPoolConnection().getConnection();
		
		try{
			
//			String sentencia = "INSERT into tempdb.dbo.tarjetas (dni, numero, limite) values (?,?,?)";
			String sentencia = "INSERT into parcialTarjetas.dbo.tarjetas (dni, numero, limite) values (?,?,?)";
			PreparedStatement ps = null;
			ps = con.prepareStatement(sentencia);
			ps.setString(1, dni);
			ps.setInt(2, tarjeta.getNumero());
			ps.setInt(3, tarjeta.getLimite());
			ps.execute();
			PoolConnection.getPoolConnection().closeConnections();
			System.out.println("Tarjeta creada con exito!! :-D");
		}
		
		catch (SQLException e) {
			System.out.println("Mensaje Error al Insertar Tarjeta: " + e.getMessage());
			System.out.println("Stack Trace al Insertar Tarjeta: " + e.getStackTrace());
			PoolConnection.getPoolConnection().closeConnections();
		}
		
		
	}
	
	public Tarjeta  buscarTarjeta (int numero){
		Connection con = PoolConnection.getPoolConnection().getConnection();
		Tarjeta t = null;
		
		try {
			
//			String sentencia = "select numero, limite from tempdb.dbo.tarjetas where numero = ?";
			String sentencia = "select numero, limite from parcialTarjetas.dbo.tarjetas where numero = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(sentencia);
			ps.setInt(1, numero);
			ResultSet rs =  ps.executeQuery();
			
			while (rs.next()){
				t = new Tarjeta();
				t.setLimite(rs.getInt(2));
				t.setNumero(rs.getInt(1));
			}
			PoolConnection.getPoolConnection().closeConnections();
		}
		catch (SQLException e){
			System.out.println("Mensaje Error al Insertar Tarjeta: " + e.getMessage());
			System.out.println("Stack Trace al Insertar Tarjeta: " + e.getStackTrace());
			PoolConnection.getPoolConnection().closeConnections();
		}
		return t;
	}
	
	
	public Vector<Tarjeta> consultarTarjetaCliente (String dni){
		
		Connection con = PoolConnection.getPoolConnection().getConnection();
		
		Vector<Tarjeta> tarjetas = new Vector<Tarjeta>();
		
		try {
			String sentencia = "Select numero, limite from parcialTarjetas.dbo.tarjetas where dni = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(sentencia);
			
			ps.setString(1, dni);
			
			ResultSet rs  = ps.executeQuery();
			
			while (rs.next()){
				Tarjeta t = new Tarjeta(rs.getInt(1), rs.getInt(2));
				tarjetas.add(t);
			}
			
			PoolConnection.getPoolConnection().closeConnections();
			
			
		}
		
		catch (SQLException e){
			System.out.println("Mensaje Error al Insertar Tarjeta: " + e.getMessage());
			System.out.println("Stack Trace al Insertar Tarjeta: " + e.getStackTrace());
			PoolConnection.getPoolConnection().closeConnections();
		}
		return tarjetas;
	}

}
